package com.gitee.kamismile.stoneComEx.util.excel;

import com.gitee.kamismile.stone.commmon.util.ValueUtils;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

public class ExcelReadUtils {

    private ExcelReadUtils() {
    }

    public static List<ArrayList<String>> readLinesByIO(InputStream is) throws Exception {
        Workbook wb = null;
        try {
            wb = new XSSFWorkbook(is);
        } catch (Exception e) {
            wb = new HSSFWorkbook(is);
        }
        try {
            Sheet sheet = wb.getSheetAt(0);
            return readLines(sheet);
        } finally {
            if (ValueUtils.isNotNull(is)) {
                IOUtils.closeQuietly(is);
            }
        }

    }


    public static List<ArrayList<String>> readLines(Sheet sheet) {
        List<ArrayList<String>> lines = new ArrayList<ArrayList<String>>();
        sheet.rowIterator().forEachRemaining(v -> {
            ArrayList<String> line = new ArrayList<String>();
            Row row = v;
            short lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                line.add(getCellStringValue(cell));
            }
            lines.add(line);
        });
        return lines;
    }


    private static String getCellStringValue(Cell cell) {
        DecimalFormat df = new DecimalFormat();
        if (cell == null)
            return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                try {
                    return df.parse(String.valueOf(cell.getNumericCellValue()))
                            .toString().trim();
                } catch (ParseException e) {
                    e.printStackTrace();
                }
            case FORMULA:
              return   cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator().evaluate(cell).formatAsString();
            default:
                return cell.getStringCellValue().trim();
        }
    }
}
